import { query, beginTransaction, commitTransaction, rollbackTransaction } from '../config/database';
import mysql from 'mysql2/promise';

export interface ModelAttributes {
  [key: string]: any;
}

export interface WhereCondition {
  [key: string]: any;
}

export interface OrderBy {
  field: string;
  direction: 'ASC' | 'DESC';
}

export interface PaginationOptions {
  page: number;
  limit: number;
}

export interface QueryResult {
  data: any[];
  total: number;
  page: number;
  limit: number;
  totalPages: number;
}

export abstract class BaseModel {
  protected tableName: string;
  protected primaryKey: string = 'id';

  constructor(tableName: string) {
    this.tableName = tableName;
  }

  /**
   * 执行原生SQL查询
   * @param sql SQL语句
   * @param params 参数数组
   */
  protected async query(sql: string, params: any[] = []): Promise<any> {
    return await query(sql, params);
  }

  /**
   * 根据ID查找单条记录
   * @param id 记录ID
   * @param fields 要查询的字段，默认为所有字段
   */
  async findById(id: number | string, fields: string = '*'): Promise<any> {
    const sql = `SELECT ${fields} FROM ${this.tableName} WHERE ${this.primaryKey} = ?`;
    const results = await this.query(sql, [id]);
    return results.length > 0 ? results[0] : null;
  }

  /**
   * 根据条件查找单条记录
   * @param where 查询条件
   * @param fields 要查询的字段，默认为所有字段
   */
  async findOne(where: WhereCondition, fields: string = '*'): Promise<any> {
    const whereClause = this.buildWhereClause(where);
    const sql = `SELECT ${fields} FROM ${this.tableName} WHERE ${whereClause.sql} LIMIT 1`;
    const results = await this.query(sql, whereClause.params);
    return results.length > 0 ? results[0] : null;
  }

  /**
   * 根据条件查找多条记录
   * @param where 查询条件
   * @param fields 要查询的字段，默认为所有字段
   * @param orderBy 排序条件
   * @param limit 限制返回的记录数
   */
  async findMany(
    where: WhereCondition = {},
    fields: string = '*',
    orderBy?: OrderBy[],
    limit?: number
  ): Promise<any[]> {
    const whereClause = this.buildWhereClause(where);
    let sql = `SELECT ${fields} FROM ${this.tableName}`;
    
    if (whereClause.sql) {
      sql += ` WHERE ${whereClause.sql}`;
    }
    
    if (orderBy && orderBy.length > 0) {
      const orderClause = orderBy.map(order => `${order.field} ${order.direction}`).join(', ');
      sql += ` ORDER BY ${orderClause}`;
    }
    
    if (limit) {
      sql += ` LIMIT ${limit}`;
    }
    
    return await this.query(sql, whereClause.params);
  }

  /**
   * 分页查询
   * @param where 查询条件
   * @param pagination 分页选项
   * @param fields 要查询的字段，默认为所有字段
   * @param orderBy 排序条件
   */
  async findWithPagination(
    where: WhereCondition = {},
    pagination: PaginationOptions,
    fields: string = '*',
    orderBy?: OrderBy[]
  ): Promise<QueryResult> {
    const { page, limit } = pagination;
    const offset = (page - 1) * limit;
    
    // 查询总数
    const whereClause = this.buildWhereClause(where);
    let countSql = `SELECT COUNT(*) as total FROM ${this.tableName}`;
    
    if (whereClause.sql) {
      countSql += ` WHERE ${whereClause.sql}`;
    }
    
    const countResult = await this.query(countSql, whereClause.params);
    const total = countResult[0].total;
    
    // 查询数据
    let dataSql = `SELECT ${fields} FROM ${this.tableName}`;
    
    if (whereClause.sql) {
      dataSql += ` WHERE ${whereClause.sql}`;
    }
    
    if (orderBy && orderBy.length > 0) {
      const orderClause = orderBy.map(order => `${order.field} ${order.direction}`).join(', ');
      dataSql += ` ORDER BY ${orderClause}`;
    }
    
    dataSql += ` LIMIT ${limit} OFFSET ${offset}`;
    
    const data = await this.query(dataSql, whereClause.params);
    
    return {
      data,
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit)
    };
  }

  /**
   * 创建新记录
   * @param data 要创建的数据
   */
  async create(data: ModelAttributes): Promise<any> {
    const fields = Object.keys(data).join(', ');
    const placeholders = Object.keys(data).map(() => '?').join(', ');
    const values = Object.values(data);
    
    const sql = `INSERT INTO ${this.tableName} (${fields}) VALUES (${placeholders})`;
    const result = await this.query(sql, values);
    
    // 返回插入的记录
    return await this.findById(result.insertId);
  }

  /**
   * 根据ID更新记录
   * @param id 记录ID
   * @param data 要更新的数据
   */
  async updateById(id: number | string, data: ModelAttributes): Promise<any> {
    const setClause = Object.keys(data).map(key => `${key} = ?`).join(', ');
    const values = [...Object.values(data), id];
    
    const sql = `UPDATE ${this.tableName} SET ${setClause} WHERE ${this.primaryKey} = ?`;
    await this.query(sql, values);
    
    // 返回更新后的记录
    return await this.findById(id);
  }

  /**
   * 根据条件更新记录
   * @param where 更新条件
   * @param data 要更新的数据
   */
  async updateMany(where: WhereCondition, data: ModelAttributes): Promise<number> {
    const whereClause = this.buildWhereClause(where);
    const setClause = Object.keys(data).map(key => `${key} = ?`).join(', ');
    const values = [...Object.values(data), ...whereClause.params];
    
    const sql = `UPDATE ${this.tableName} SET ${setClause} WHERE ${whereClause.sql}`;
    const result = await this.query(sql, values);
    
    return result.affectedRows;
  }

  /**
   * 根据ID删除记录
   * @param id 记录ID
   */
  async deleteById(id: number | string): Promise<boolean> {
    const sql = `DELETE FROM ${this.tableName} WHERE ${this.primaryKey} = ?`;
    const result = await this.query(sql, [id]);
    return result.affectedRows > 0;
  }

  /**
   * 根据条件删除记录
   * @param where 删除条件
   */
  async deleteMany(where: WhereCondition): Promise<number> {
    const whereClause = this.buildWhereClause(where);
    const sql = `DELETE FROM ${this.tableName} WHERE ${whereClause.sql}`;
    const result = await this.query(sql, whereClause.params);
    return result.affectedRows;
  }

  /**
   * 构建WHERE子句
   * @param where 查询条件
   */
  private buildWhereClause(where: WhereCondition): { sql: string; params: any[] } {
    if (!where || Object.keys(where).length === 0) {
      return { sql: '', params: [] };
    }
    
    const conditions: string[] = [];
    const params: any[] = [];
    
    for (const [key, value] of Object.entries(where)) {
      if (value === null) {
        conditions.push(`${key} IS NULL`);
      } else if (Array.isArray(value)) {
        const placeholders = value.map(() => '?').join(', ');
        conditions.push(`${key} IN (${placeholders})`);
        params.push(...value);
      } else {
        conditions.push(`${key} = ?`);
        params.push(value);
      }
    }
    
    return {
      sql: conditions.join(' AND '),
      params
    };
  }

  /**
   * 开始事务
   */
  async beginTransaction(): Promise<mysql.PoolConnection> {
    return await beginTransaction();
  }

  /**
   * 提交事务
   */
  async commitTransaction(connection: mysql.PoolConnection): Promise<void> {
    await commitTransaction(connection);
  }

  /**
   * 回滚事务
   */
  async rollbackTransaction(connection: mysql.PoolConnection): Promise<void> {
    await rollbackTransaction(connection);
  }
}